﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class InvestDesciplineBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);



        private string formatDate(string value)
        {
            string tmp = null;
            string[] tmp2 = null;
            tmp2 = value.Split('/');
            tmp = tmp2[1] + "/" + tmp2[0] + "/" + tmp2[2];

            return tmp;
        }






        public void insert( int inv_seq,string inv_name,string inv_ref_no,string inv_date,int appeals,string app_date,string app_result,int created_by)
        {
            try
            {
                string invDate, appDate;

                invDate = formatDate(inv_date);
                appDate = formatDate(app_date);
                DateTime inv_date_tmp = Convert.ToDateTime(invDate);
                DateTime app_date_tmp = Convert.ToDateTime(appDate);


                dbManager.Open();
                dbManager.CreateParameters(10);
                dbManager.AddParameters(0, "@inv_ref_seq", inv_seq);
                dbManager.AddParameters(1, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(2, "@inv_name", inv_name);
                dbManager.AddParameters(3, "@inv_date", inv_date_tmp);
                dbManager.AddParameters(4, "@appeals", appeals);
                dbManager.AddParameters(5, "@app_date", app_date_tmp);
                dbManager.AddParameters(6, "@app_result", app_result);
                dbManager.AddParameters(7, "@record_status",SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Today);
          
                string sql;

                sql = " insert into "
                + " hrm_dc_investigations "

                + " (inv_type_seq,"
                + " inv_name,"
                + " inv_ref_seq,"
                + " inv_ref_no,"
                + " inv_date,"
                + " appeals,"
                + " app_date,"
                + " app_result,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (2,"
                + " @inv_name," 
                + " @inv_ref_seq,"
                + " @inv_ref_no,"
                + " @inv_date,"
                + " @appeals,"
                + " @app_date,"
                + " @app_result,"
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }

        public void insertNoAppeal(int inv_seq, string inv_name, string inv_ref_no, string inv_date, int appeals,  int created_by)
        {
            try
            {
                string invDate;

                invDate = formatDate(inv_date);

                DateTime inv_date_tmp = Convert.ToDateTime(invDate);
    


                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@inv_ref_seq", inv_seq);
                dbManager.AddParameters(1, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(2, "@inv_name", inv_name);
                dbManager.AddParameters(3, "@inv_date", inv_date_tmp);
                dbManager.AddParameters(4, "@appeals", appeals);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@created_by", created_by);
                dbManager.AddParameters(7, "@created_when", DateTime.Today);

                string sql;

                sql = " insert into "
                + " hrm_dc_investigations "

                + " (inv_type_seq,"
                + " inv_name,"
                + " inv_ref_seq,"
                + " inv_ref_no,"
                + " inv_date,"
                + " appeals,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (2,"
                + " @inv_name,"
                + " @inv_ref_seq,"
                + " @inv_ref_no,"
                + " @inv_date,"
                + " @appeals,"
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }

        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT *"
                    +  " from hrm_dc_investigations"
                    + " where inv_type_seq = 2 "
                    + " and record_status = 'U'";

                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_dc_statuses where record_status = '" + SystemInfo._ActiveRecordStatus  + "'"  ;
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


        public DataTable SelectComboParent()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_dc_investigations "
                + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                + " and inv_type_seq = 1 ";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public void Update(int dataKey,string inv_name,int inv_seq, string inv_ref_no, string inv_date, int appeals, string app_date, string app_result, int created_by)
        {
            try
            {
                string invDate, appDate;

                invDate = formatDate(inv_date);
                appDate = formatDate(app_date);
                DateTime inv_date_tmp = Convert.ToDateTime(invDate);
                DateTime app_date_tmp = Convert.ToDateTime(appDate);



                dbManager.Open();
                dbManager.CreateParameters(11);
                dbManager.AddParameters(0, "@dataKey", dataKey );
                dbManager.AddParameters(1, "@inv_name", inv_name);
                dbManager.AddParameters(2, "@inv_ref_seq", inv_seq);
                dbManager.AddParameters(3, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(4, "@inv_date", inv_date_tmp);
                dbManager.AddParameters(5, "@appeals", appeals);
                dbManager.AddParameters(6, "@app_date", app_date_tmp);
                dbManager.AddParameters(7, "@app_result", app_result);
                dbManager.AddParameters(8, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@created_by", created_by);
                dbManager.AddParameters(10, "@created_when", DateTime.Today);

                string sql;

                sql = " update "
                + " hrm_dc_investigations "

                + " set inv_ref_seq=@inv_ref_seq,"
                + " inv_name=@inv_name,"
                + " inv_ref_no=@inv_ref_no,"
                + " inv_date=@inv_date,"
                + " appeals=@appeals,"
                + " app_date=@app_date,"
                + " app_result=@app_result,"
                + " record_status=@record_status,"
                + " created_by=@created_by,"
                + " created_when=@created_when"
                + " where inv_seq=@dataKey";

                

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }


        public void UpdateNoAppeal(int dataKey, string inv_name, int inv_seq, string inv_ref_no, string inv_date, int appeals, int created_by)
        {
            try
            {
                string invDate, appDate;

                invDate = formatDate(inv_date);

                DateTime inv_date_tmp = Convert.ToDateTime(invDate);
    

                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@dataKey", dataKey);
                dbManager.AddParameters(1, "@inv_name", inv_name);
                dbManager.AddParameters(2, "@inv_ref_seq", inv_seq);
                dbManager.AddParameters(3, "@inv_ref_no", inv_ref_no);
                dbManager.AddParameters(4, "@inv_date", inv_date_tmp);
                dbManager.AddParameters(5, "@appeals", appeals);
                 dbManager.AddParameters(6, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@created_by", created_by);
                dbManager.AddParameters(8, "@created_when", DateTime.Today);

                string sql;

                sql = " update "
                + " hrm_dc_investigations "

                + " set inv_ref_seq=@inv_ref_seq,"
                + " inv_name=@inv_name,"
                + " inv_ref_no=@inv_ref_no,"
                + " inv_date=@inv_date,"
                + " appeals=@appeals,"
                + " record_status=@record_status,"
                + " created_by=@created_by,"
                + " created_when=@created_when"
                + " where inv_seq=@dataKey";



                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }




        public void Delete(int inv_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inv_seq", inv_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
              dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
                //sql = " update hrm_dc_penalty set "
                //+ " record_status=@record_status"
                //+ ", updated_by = @updated_by"
                //+ ", updated_when = @updated_when "
                //  + " where pen_seq = @pen_seq";


                sql = " update hrm_dc_investigations set "
              + " record_status=@record_status,"
              + " updated_by = @updated_by,"
              + " updated_when = @updated_when"
              + " where inv_seq = @inv_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
                
                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        /////////////////////     POPUP  /////////////////////////

        public Boolean chkDup(int inv_seq,int inv_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;



                sql = " SELECT "
                    + " hrm_dc_investigation_dtls.inv_dtls_seq,"
                    + " concat(mas_name_prefixes.prefix_name,'  ',hrm_ps_employees.first_name,'   ',hrm_ps_employees.last_name) as fullname,"
                    + " mas_name_prefixes.prefix_name,"
+ " hrm_ps_employees.first_name,"
+ " hrm_ps_employees.last_name,"
+ " hrm_mp_positions.pos_name,"
+ " hrm_mp_classes.class_name,"
+ " isNULL(hrm_ps_employees.working_job_site_seq,hrm_mp_job_sites3.job_site_seq) AS working_job_site_seq,"
+ " isNULL(hrm_mp_job_sites2.job_site_name,hrm_mp_job_sites3.job_site_name) AS working_job_site_name,"
+ " hrm_mp_job_sites3.job_site_seq,"
+ " hrm_mp_job_sites3.job_site_name,"
+ " hrm_mp_positions.pos_ref,"

+ " hrm_dc_investigation_dtls.inv_emp_seq,"
+ " hrm_dc_investigation_dtls.crd_seq,"
+ " hrm_dc_investigation_dtls.crd_descr,"
+ " hrm_dc_investigation_dtls.remarks"

+ " FROM"
+ " hrm_ps_employees"
+ " Inner Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq"
+ " Inner Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq"
+ " Inner Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq"
+ " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites2.job_site_seq"
+ " Inner Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_mp_positions.job_site_seq = hrm_mp_job_sites3.job_site_seq"
+ " Inner Join hrm_dc_investigation_dtls ON hrm_dc_investigation_dtls.inv_emp_seq = hrm_ps_employees.emp_seq"
+ " Inner Join hrm_dc_investigations ON hrm_dc_investigations.inv_seq = hrm_dc_investigation_dtls.inv_seq"
+ " where hrm_dc_investigation_dtls.record_status = 'U'"
+ " and hrm_dc_investigations.inv_type_seq=2"
+ " and hrm_dc_investigation_dtls.inv_seq = " + inv_seq
+ " and hrm_dc_investigation_dtls.inv_emp_seq = " + inv_emp_seq ;


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                    return false ;
            }

            catch (Exception ex)
            {
                return true;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelecDetails(int inv_seq)
        {
            try
            {
                dbManager.Open();
                string sql;



                sql = " SELECT "
                    + " hrm_dc_investigation_dtls.inv_dtls_seq,"
                    + " concat(mas_name_prefixes.prefix_name,'  ',hrm_ps_employees.first_name,'   ',hrm_ps_employees.last_name) as fullname,"
                    + " mas_name_prefixes.prefix_name,"
+ " hrm_ps_employees.first_name,"
+ " hrm_ps_employees.last_name,"
+ " hrm_mp_positions.pos_name,"
+ " hrm_mp_classes.class_name,"
+ " isNULL(hrm_ps_employees.working_job_site_seq,hrm_mp_job_sites3.job_site_seq) AS working_job_site_seq,"
+ " isNULL(hrm_mp_job_sites2.job_site_name,hrm_mp_job_sites3.job_site_name) AS working_job_site_name,"
+ " hrm_mp_job_sites3.job_site_seq,"
+ " hrm_mp_job_sites3.job_site_name,"
+ " hrm_mp_positions.pos_ref,"

+ " hrm_dc_investigation_dtls.inv_emp_seq,"
+ " hrm_dc_investigation_dtls.crd_seq,"
+ " hrm_dc_investigation_dtls.crd_descr,"
+ " hrm_dc_investigation_dtls.remarks"

+ " FROM"
+ " hrm_ps_employees"
+ " Left Join hrm_mp_positions ON hrm_ps_employees.pos_seq = hrm_mp_positions.pos_seq"  // change inner join to left join
+ " Left Join hrm_mp_classes ON hrm_ps_employees.class_seq = hrm_mp_classes.class_seq"  // change inner join to left join
+ " Left Join mas_name_prefixes ON hrm_ps_employees.name_prefix_seq = mas_name_prefixes.name_prefix_seq"  // change inner join to left join
+ " Left Outer Join hrm_mp_job_sites AS hrm_mp_job_sites2 ON hrm_ps_employees.working_job_site_seq = hrm_mp_job_sites2.job_site_seq"
+ " Left Join hrm_mp_job_sites AS hrm_mp_job_sites3 ON hrm_mp_positions.job_site_seq = hrm_mp_job_sites3.job_site_seq"  // change inner join to left join
+ " Left Join hrm_dc_investigation_dtls ON hrm_dc_investigation_dtls.inv_emp_seq = hrm_ps_employees.emp_seq"  // change inner join to left join
+ " Left Join hrm_dc_investigations ON hrm_dc_investigations.inv_seq = hrm_dc_investigation_dtls.inv_seq"  // change inner join to left join
+ " where hrm_dc_investigation_dtls.record_status = 'U'"
+ " and hrm_dc_investigations.inv_type_seq=2"
+ " and hrm_dc_investigation_dtls.inv_seq = " + inv_seq ;


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectComboCrime()
        {

            try
            {
                dbManager.Open();
                string sql;

                sql = " SELECT * From HRM_DC_CRIME_DTLS ";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public void InsertDtls(int inv_seq, int emp_seq,int crd_seq,string crd_desc,string remarks, string created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@inv_seq", inv_seq);
                dbManager.AddParameters(1, "@inv_emp_seq", emp_seq);
                dbManager.AddParameters(2, "@crd_seq", crd_seq);
                dbManager.AddParameters(3, "@crd_desc", crd_desc);
                dbManager.AddParameters(4, "@remarks", remarks);
                dbManager.AddParameters(5, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@created_by", created_by);
                dbManager.AddParameters(7, "@created_when", DateTime.Today);


                string sql;

                sql = " insert into "
                + " hrm_dc_investigation_dtls "
                + " (inv_seq,"
                + " inv_emp_seq,"
                + " crd_seq,"
                + " crd_descr,"
                + " remarks,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (@inv_seq,"
                + " @inv_emp_seq,"
                + " @crd_seq,"
                + " @crd_desc,"
                + " @remarks,"
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }

        public void UpdateDtls(int inv_dtls_seq, int emp_seq, int crd_seq, string crd_desc, string remarks, string updated_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@inv_dtls_seq", inv_dtls_seq);
                dbManager.AddParameters(1, "@inv_emp_seq", emp_seq);
                dbManager.AddParameters(2, "@crd_seq", crd_seq);
                dbManager.AddParameters(3, "@crd_desc", crd_desc);
                dbManager.AddParameters(4, "@remarks", remarks);
                dbManager.AddParameters(5, "@updated_by", updated_by);
                dbManager.AddParameters(6, "@updated_when", DateTime.Today);


                string sql;

                sql = " update "
                + " hrm_dc_investigation_dtls "

                + " set inv_emp_seq=@inv_emp_seq,"
                + " crd_seq=@crd_seq,"
                + " crd_descr=@crd_desc,"
                + " remarks=@remarks,"

                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"

                + " where inv_dtls_seq=@inv_dtls_seq";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }
       
        public void DeleteDtls(int seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@inv_dtls_seq", seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_dc_investigation_dtls set "
               + " record_status=@record_status,"
               + " updated_by = @updated_by,"
               + " updated_when = @updated_when"
               + " where inv_dtls_seq = @inv_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

                //   dal.Delete(Sequence, UpdatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }



  







     

   
    }
}
